﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Microsoft.ApplicationBlocks.Data;

namespace ModuleDuoc
{
    public partial class frmKiemKe : WeifenLuo.WinFormsUI.Docking.DockContent
    {
        string soluongcu = "0";
        DataTable _dt = new DataTable();
        public frmKiemKe()
        {
            InitializeComponent();
        }
        private void frmKiemKe_Load(object sender, EventArgs e)
        {
            dgvData.AutoGenerateColumns = false;
            loadInfoData();
            this.txtThang.Text = DateTime.Now.Month.ToString();
            this.txtNam.Text = DateTime.Now.Year.ToString();
        }

        /// <summary>
        /// Load thong tin
        /// </summary>
        private void loadInfoData()
        {
            string sql_Check = "select * from zD_KIEMKE2012 where thang='" + DateTime.Now.Month + "' and nam='" + DateTime.Now.Year + "'";
            DataTable dt_check = SqlHelper.ExecuteDataset(
                System.Configuration.ConfigurationManager.AppSettings["Main.ConnectionString"],
                CommandType.Text,
                sql_Check).Tables[0];
            if (dt_check.Rows.Count > 0)
            {
                this.btnLoad.Enabled = false;
                string sql = "select * from thongke.view_kiemke where thang='" + DateTime.Now.Month + "' and nam='" + DateTime.Now.Year + "' order by tennhom";
                DataTable dt = SqlHelper.ExecuteDataset(
                    System.Configuration.ConfigurationManager.AppSettings["Main.ConnectionString"],
                    CommandType.Text,
                    sql).Tables[0];
                this.dgvData.DataSource = dt;
                _dt = dt;
                for (int i = 0; i < dgvData.Rows.Count; i++)
                {
                    if (dgvData.Rows[i].Cells["soluong"].Value.ToString() != "0")
                    {
                        dgvData.Rows[i].DefaultCellStyle.BackColor = Color.Yellow;
                    }
                    //dgvData.Rows[i].Cells["lythuyet"].Value = get_tonlythuyet(dgvData.Rows[i].Cells["mavach"].Value.ToString()).ToString();
                }
            }
            else
                this.btnLoad.Enabled = true;
        }

        /// <summary>
        /// Update số lương tồn
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void dgvData_CellEndEdit(object sender, DataGridViewCellEventArgs e)
        {
            string sql_update = "";
            if (this.dgvData.Rows[dgvData.CurrentRow.Index].Cells["soluong"].Value.ToString() != soluongcu)
            {
                if (bool.Parse(this.dgvData.Rows[dgvData.CurrentRow.Index].Cells["fix"].Value.ToString()) == false)
                {
                    try
                    {
                        sql_update = "UPDATE zD_KIEMKE2012 set soluong='";
                        sql_update += dgvData.Rows[dgvData.CurrentRow.Index].Cells["soluong"].Value;
                        sql_update += "' where mavach='" + dgvData.Rows[dgvData.CurrentRow.Index].Cells["mavach"].Value;
                        sql_update += "' and thang='" + DateTime.Now.Month;
                        sql_update += "' and nam='" + DateTime.Now.Year + "'";
                        SqlHelper.ExecuteNonQuery(
                            System.Configuration.ConfigurationManager.AppSettings["Main.ConnectionString"],
                            CommandType.Text,
                            sql_update);
                        this.dgvData.Rows[dgvData.CurrentRow.Index].Cells["status"].Value = "Updated";
                        this.dgvData.Rows[dgvData.CurrentRow.Index].DefaultCellStyle.BackColor = Color.Yellow;
                    }
                    catch { }
                    {
                        //this.dgvData.Rows[dgvData.CurrentRow.Index].DefaultCellStyle.BackColor = Color.Red;
                    }
                }
                else
                {
                    MessageBox.Show("Dữ liệu đã chuyển tồn, không thể thay đổi!", "Thông báo");
                    this.dgvData.Rows[dgvData.CurrentRow.Index].Cells["soluong"].Value = soluongcu;
                }
            }
        }

        private void btnLoad_Click(object sender, EventArgs e)
        {
            string sql = "select * from thongke.view_kiemke_insert";
            DataTable dt = SqlHelper.ExecuteDataset(
                System.Configuration.ConfigurationManager.AppSettings["Main.ConnectionString"],
                CommandType.Text,
                sql).Tables[0];
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    try
                    {
                        string sql_insert = "INSERT INTO [zD_KIEMKE2012] ";
                        sql_insert += "([mavach]";
                        sql_insert += ",[soluong]";
                        sql_insert += ",[thang]";
                        sql_insert += ",[nam]";
                        sql_insert += ",[fix])";
                        sql_insert += "VALUES";
                        sql_insert += "('" + dt.Rows[i]["mavach"].ToString() + "'";
                        sql_insert += " ,'0'";
                        sql_insert += " ,'" + DateTime.Now.Month.ToString() + "'";
                        sql_insert += ",'" + DateTime.Now.Year.ToString() + "'";
                        sql_insert += " ,0)";
                        SqlHelper.ExecuteNonQuery(
                            System.Configuration.ConfigurationManager.AppSettings["Main.ConnectionString"],
                            CommandType.Text,
                            sql_insert);
                    }
                    catch { }
                }
            }
            loadInfoData();
        }

        private void dgvData_CellBeginEdit(object sender, DataGridViewCellCancelEventArgs e)
        {
            this.dgvData.Rows[dgvData.CurrentRow.Index].Cells["status"].Value = "Đã chọn";
            soluongcu = dgvData.Rows[dgvData.CurrentRow.Index].Cells["soluong"].Value.ToString();
        }

        private void radAll_CheckedChanged(object sender, EventArgs e)
        {
            _checked("");
        }
        private void _checked(string filter)
        {
            string sql_Check = "select * from zD_KIEMKE2012 where thang='" + DateTime.Now.Month + "' and nam='" + DateTime.Now.Year + "'" + filter;
            DataTable dt_check = SqlHelper.ExecuteDataset(
                System.Configuration.ConfigurationManager.AppSettings["Main.ConnectionString"],
                CommandType.Text,
                sql_Check).Tables[0];
            if (dt_check.Rows.Count > 0)
            {
                string sql = "select * from thongke.view_kiemke where thang='" + DateTime.Now.Month + "' and nam='" + DateTime.Now.Year + "'" + filter;
                DataTable dt = SqlHelper.ExecuteDataset(
                    System.Configuration.ConfigurationManager.AppSettings["Main.ConnectionString"],
                    CommandType.Text,
                    sql).Tables[0];
                this.dgvData.DataSource = dt;
                for (int i = 0; i < dgvData.Rows.Count; i++)
                {
                    if (dgvData.Rows[i].Cells["soluong"].Value.ToString() != "0")
                    {
                        dgvData.Rows[i].DefaultCellStyle.BackColor = Color.Yellow;
                    }
                }
            }
        }

        private void radTonbang0_CheckedChanged(object sender, EventArgs e)
        {
            _checked(" and soluong=0");
        }

        private void radTonHon0_CheckedChanged(object sender, EventArgs e)
        {
            _checked(" and soluong >0");
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("Xác nhận thoát?", "Thông báo", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
                this.Close();
        }

        private void btnChuyenTon_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("Xác nhận chuyển tồn? Sau khi chuyển tồn, dữ liệu tồn sẽ căn cứ tính toán dữ liệu bán hàng?", "Thông báo", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
            {
                for (int i = 0; i < dgvData.Rows.Count; i++)
                {
                    string sql_update = "";
                    sql_update = "UPDATE zD_KIEMKE2012 set fix=1";
                    sql_update += " where mavach='" + dgvData.Rows[i].Cells["mavach"].Value;
                    sql_update += "' and thang='" + DateTime.Now.Month;
                    sql_update += "' and nam='" + DateTime.Now.Year + "'";
                    SqlHelper.ExecuteNonQuery(
                        System.Configuration.ConfigurationManager.AppSettings["Main.ConnectionString"],
                        CommandType.Text,
                        sql_update);
                    dgvData.Rows[i].Cells["status"].Value = "Đã chuyển tồn";
                }
            }
        }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            searchThuoc();
        }

        private void searchThuoc()
        {
            string sql = "select * from thongke.view_kiemke where (mavach like N'%" + this.txtMavach.Text.Trim() + "%' or tenvt like N'%" + this.txtMavach.Text.Trim() + "%' or tennhom like '%" + this.txtMavach.Text.Trim() + "%') and thang='" + DateTime.Now.Month.ToString() + "' and nam='" + DateTime.Now.Year + "' order by thang,nam desc";
            DataTable dt = SqlHelper.ExecuteDataset(
                System.Configuration.ConfigurationManager.AppSettings["Main.ConnectionString"],
                CommandType.Text,
                sql).Tables[0];
            this.dgvData.DataSource = dt;
            for (int i = 0; i < dgvData.Rows.Count; i++)
            {
                if (dgvData.Rows[i].Cells["soluong"].Value.ToString() != "0")
                {
                    dgvData.Rows[i].DefaultCellStyle.BackColor = Color.Yellow;
                }
                //dgvData.Rows[i].Cells["lythuyet"].Value = get_tonlythuyet(dgvData.Rows[i].Cells["mavach"].Value.ToString()).ToString();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="mavach"></param>
        /// <returns></returns>
        private int get_tonlythuyet(string mavach)
        {
            string sql = "SELECT  ISNULL";
            sql += "((SELECT SUM(soluong) AS Expr1";
            sql += " FROM dbo.zD_KIEMKE2012";
            sql += " WHERE (mavach = " + mavach + ") AND (thang = 5) AND (nam = 2013)) +";
            sql += " (SELECT SUM(ISNULL(dbo.zD_PHIEUNHAPKHOCT.soluong, 0)) AS Expr1";
            sql += " FROM dbo.zD_PHIEUNHAPKHOCT INNER JOIN";
            sql += " dbo.zD_PHIEUNHAPKHO ON ";
            sql += " dbo.zD_PHIEUNHAPKHOCT.sophieu = dbo.zD_PHIEUNHAPKHO.sophieu";
            sql += " WHERE (dbo.zD_PHIEUNHAPKHOCT.mavach = " + mavach + ") AND (MONTH(dbo.zD_PHIEUNHAPKHO.ngaylap) = 5)";
            sql += " AND (YEAR(dbo.zD_PHIEUNHAPKHO.ngaylap) = 2013)) -";
            sql += " (SELECT SUM(ISNULL(dbo.zD_PHIEUXUATKHOCT.soluong, 0)) AS Expr1";
            sql += " FROM dbo.zD_PHIEUXUATKHOCT INNER JOIN";
            sql += " dbo.zD_PHIEUXUATKHO ON ";
            sql += " dbo.zD_PHIEUXUATKHOCT.sophieu = dbo.zD_PHIEUXUATKHO.sophieu";
            sql += " WHERE (dbo.zD_PHIEUXUATKHOCT.mavach = " + mavach + ") AND (MONTH(dbo.zD_PHIEUXUATKHO.ngaylap) = 5) ";
            sql += " AND (YEAR(dbo.zD_PHIEUXUATKHO.ngaylap) = 5)), 0) AS tonkho";
            DataTable dt = SqlHelper.ExecuteDataset(
                System.Configuration.ConfigurationManager.AppSettings["Main.ConnectionString"],
                CommandType.Text,
                sql).Tables[0];
            if (dt.Rows.Count > 0)
            {
                return int.Parse(dt.Rows[0]["tonkho"].ToString());
            }
            else
            {
                return 0;
            }
        }
        private void txtMavach_TextChanged(object sender, EventArgs e)
        {
            searchThuoc();
        }

        private void btnXuatExcel_Click(object sender, EventArgs e)
        {
            DataTable dt = new DataTable();
            dt = _dt;
            System.Windows.Forms.SaveFileDialog saveFileDialog1 = new System.Windows.Forms.SaveFileDialog();
            saveFileDialog1.InitialDirectory = "C:\\";
            saveFileDialog1.Title = "Save Files";
            saveFileDialog1.CheckFileExists = false;
            saveFileDialog1.CheckPathExists = true;
            saveFileDialog1.DefaultExt = "xls";
            saveFileDialog1.Filter = "Excel 97-2003 files (*.xls)|*.xls|Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
            saveFileDialog1.FilterIndex = 1;

            saveFileDialog1.RestoreDirectory = true;

            string fileName = "";
            if (saveFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                fileName = saveFileDialog1.FileName;
                System.Web.UI.WebControls.DataGrid grid = new System.Web.UI.WebControls.DataGrid();
                grid.HeaderStyle.Font.Bold = true;
                grid.DataSource = dt;
                grid.DataMember = dt.TableName;
                grid.DataBind();
                using (System.IO.StreamWriter sw = new System.IO.StreamWriter(fileName, true, System.Text.Encoding.Unicode))
                {
                    using (System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(sw))
                    {
                        grid.RenderControl(hw);
                    }
                }
                MessageBox.Show("Dữ liệu đã được xuất tại : " + fileName, "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }
    }
}
